package com.ridbor001.demo.dao;

import com.ridbor001.demo.bean.RDBCourse;
import com.ridbor001.demo.bean.RDBEvaluation;
import com.ridbor001.demo.bean.RDBEvaluationItem;
import com.ridbor001.demo.util.RDBDataStore;

import java.sql.*;
import java.util.*;

public class RDBEvaluationDAO {
    RDBDataStore dataStore = new RDBDataStore();

    public  boolean addEvaluation(RDBEvaluation evaluation, Connection conn) throws SQLException {
        String sql = "INSERT INTO evaluations (eval_id, student_id, course_id, eval_time) VALUES (?, ?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, evaluation.getEvalId());
            pstmt.setString(2, evaluation.getStudentId());
            pstmt.setString(3, evaluation.getCourseId());
            pstmt.setTimestamp(4, Timestamp.valueOf(evaluation.getEvalTime()));

            return pstmt.executeUpdate() > 0;
        }
    }

    public  void addEvaluationWithItems(RDBEvaluation evaluation) throws SQLException {
        try (Connection conn = dataStore.getConnection()) {
            conn.setAutoCommit(false);  // 关闭自动提交

            try {
                // 1. 添加评教主表记录
                if (!addEvaluation(evaluation, conn)) {
                    throw new SQLException("添加评教主表失败");
                }

                // 2. 批量添加明细项
                if (!evaluation.getItems().isEmpty()) {
                    addEvaluationItems(evaluation.getEvalId(), evaluation.getItems(), conn);
                }
                conn.commit();  // 提交事务
            } catch (SQLException e) {
                conn.rollback();  // 回滚事务
                throw new SQLException("添加评教失败: " + e.getMessage(), e);
            } finally {
                conn.setAutoCommit(true);  // 恢复自动提交
            }
        }
    }
    public static void addEvaluationItems(String evalId, List<RDBEvaluationItem> items, Connection conn) throws SQLException {
        String sql = "INSERT INTO evaluation_items (eval_id, dimension, score, comment) VALUES (?, ?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (RDBEvaluationItem item : items) {
                pstmt.setString(1, evalId);
                pstmt.setString(2, item.getDimension());
                pstmt.setInt(3, item.getScore());
                pstmt.setString(4, item.getComment());
                pstmt.addBatch();  // 添加到批处理
            }

            int[] results = pstmt.executeBatch();  // 执行批处理
            for (int result : results) {
                if (result != Statement.SUCCESS_NO_INFO && result <= 0) {
                    throw new SQLException("添加评教明细失败");
                }
            }
        }
    }
    public  List<RDBEvaluation> getTeacherEvaluations(String teacherId) throws SQLException {
        String sql = "SELECT ev.eval_id, ev.student_id, ev.course_id, ev.eval_time, "
                + "ei.dimension, ei.score, ei.comment "
                + "FROM evaluations ev "
                + "JOIN courses c ON ev.course_id = c.course_id "
                + "JOIN evaluation_items ei ON ev.eval_id = ei.eval_id "
                + "WHERE c.teacher_id = ? "
                + "ORDER BY ev.eval_id";

        Map<String, RDBEvaluation> evalMap = new HashMap<>();

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, teacherId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String evalId = rs.getString("eval_id");
                    RDBEvaluation evaluation = evalMap.get(evalId);

                    // 新建评教对象
                    if (evaluation == null) {
                        evaluation = new RDBEvaluation(
                                evalId,
                                rs.getString("student_id"),
                                rs.getString("course_id"),
                                rs.getTimestamp("eval_time").toLocalDateTime()
                        );
                        evalMap.put(evalId, evaluation);
                    }

                    // 添加评教项
                    evaluation.addItem(
                            rs.getString("dimension"),
                            rs.getInt("score"),
                            rs.getString("comment")
                    );
                }
            }
        }
        return new ArrayList<>(evalMap.values());
    }

    public   List<RDBEvaluation> getEvaluationsByCourseIds(List<String> courseIds) throws SQLException {
        if (courseIds == null || courseIds.isEmpty()) {
            return Collections.emptyList();
        }

        String placeholders = String.join(",", Collections.nCopies(courseIds.size(), "?"));
        String sql = String.format("SELECT ev.eval_id, ev.student_id, ev.course_id, ev.eval_time, "
                + "ei.dimension, ei.score, ei.comment "
                + "FROM evaluations ev "
                + "JOIN evaluation_items ei ON ev.eval_id = ei.eval_id "
                + "WHERE ev.course_id IN (%s) "
                + "ORDER BY ev.eval_id", placeholders);

        Map<String, RDBEvaluation> evalMap = new HashMap<>();

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < courseIds.size(); i++) {
                pstmt.setString(i + 1, courseIds.get(i));
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String evalId = rs.getString("eval_id");
                    RDBEvaluation evaluation = evalMap.get(evalId);

                    if (evaluation == null) {
                        evaluation = new RDBEvaluation(
                                evalId,
                                rs.getString("student_id"),
                                rs.getString("course_id"),
                                rs.getTimestamp("eval_time").toLocalDateTime()
                        );
                        evalMap.put(evalId, evaluation);
                    }

                    evaluation.addItem(
                            rs.getString("dimension"),
                            rs.getInt("score"),
                            rs.getString("comment")
                    );
                }
            }
        }
        return new ArrayList<>(evalMap.values());
    }
    public   List<RDBCourse> getEvaluatedCoursesByStudent(String studentId) throws SQLException {
        final String sql = "SELECT c.course_id, c.course_name, c.term, c.teacher_id "
                + "FROM student_courses sc "
                + "JOIN courses c ON sc.course_id = c.course_id "
                + "WHERE sc.student_id = ? AND EXISTS ("
                + "   SELECT 1 FROM evaluations e "
                + "   WHERE e.course_id = sc.course_id AND e.student_id = sc.student_id"
                + ")";

        List<RDBCourse> courses = new ArrayList<>();
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    courses.add(new RDBCourse(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getString("term"),
                            rs.getString("teacher_id")
                    ));
                }
            }
        }
        return courses;
    }


    public   List<RDBCourse> getPendingEvaluations(String studentId) throws SQLException {
        final String sql = "SELECT c.course_id, c.course_name, c.term, c.teacher_id "
                + "FROM student_courses sc "
                + "JOIN courses c ON sc.course_id = c.course_id "
                + "WHERE sc.student_id = ? AND NOT EXISTS ("
                + "   SELECT 1 FROM evaluations e "
                + "   WHERE e.course_id = sc.course_id AND e.student_id = sc.student_id"
                + ")";

        List<RDBCourse> courses = new ArrayList<>();
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    courses.add(new RDBCourse(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getString("term"),
                            rs.getString("teacher_id")
                    ));
                }
            }
        }
        return courses;
    }

}
